import cn.yunhe.entity.*;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.cfg.Configuration;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.*;

public class Demo {
    Session session = null;
    Transaction transaction = null;

    @Before
    public void bef() {
        Configuration configuration = new AnnotationConfiguration().configure();
        SessionFactory factory = configuration.buildSessionFactory();
        session = factory.openSession();
    }

    @Test
    public void test1() {
        Jd jd = (Jd) session.get(Jd.class, 3);
        String qx = jd.getQx().getQx();
        System.out.println(qx);
    }

    @Test
    public void test2() {
        Qx qx = (Qx) session.get(Qx.class, 1);
        System.out.println(qx.getQx());
        Iterator it = qx.getJds().iterator();
        while (it.hasNext()) {
            Jd jd = (Jd) it.next();
            System.out.println(jd.getQx().getQx());
        }

    }

    @Test
    public void test3() {
        transaction = session.beginTransaction();
        Qx qx = new Qx();
        qx.setQx("西区");
        session.save(qx);
        transaction.commit();
    }

    @Test
    public void test4() {
        transaction = session.beginTransaction();
        //Qx qx = (Qx) session.get(Qx.class, 1);
        Qx qx = new Qx();
        qx.setQx("KK区");
        session.save(qx);
        Jd jd1 = new Jd();
        jd1.setJd("洗三路");
        Jd jd2 = new Jd();
        jd2.setJd("kk路");
        Jd jd3 = new Jd();
        jd3.setJd("三环");
        session.save(jd1);
        session.save(jd2);
        session.save(jd3);

        qx.getJds().add(jd1);
        qx.getJds().add(jd2);
        qx.getJds().add(jd3);
        session.save(qx);
        transaction.commit();
    }

    @Test
    public void test5() {
        transaction = session.beginTransaction();
        Qx qx = (Qx) session.get(Qx.class, 1);
        session.delete(qx);
        transaction.commit();
    }

    @Test
    public void test6() {
        transaction = session.beginTransaction();
        Course course1 = new Course();
        Course course2 = new Course();
        course1.setCname("H5");
        course2.setCname("Python");
        session.save(course1);
        session.save(course2);

        Student student1 = new Student();
        Student student2 = new Student();
        student1.setSname("任涛涛");
        student2.setSname("朱田芳");


        student1.getCourses().add(course1);
        student1.getCourses().add(course2);
        student2.getCourses().add(course1);
        student2.getCourses().add(course2);
        session.save(student1);
        session.save(student2);
        transaction.commit();
    }

    @Test
    public void test7() {
        List<Jd> list = session.createQuery("from Jd ").list();
        for (Jd j : list) {
            System.out.println(j.getJd());
        }
    }

    @Test
    public void test8() {
        Qx qx = (Qx) session.get(Qx.class, 1);
        System.out.println("区县:" + qx.getQx() + ",下面的街道是:");
        Set<Jd> set = qx.getJds();
        for (Jd j : set) {
            System.out.println(j.getJd());
        }
    }

    @Test
    public void test9() {
        Student student = (Student) session.get(Student.class, 1);
        System.out.print(student.getSname());
        System.out.println("所选课程:");
        Set<Course> set = student.getCourses();
        for (Course c : set) {
            System.out.println(c.getCname());
        }
        System.out.println("--------------------------");

        Course course = (Course) session.get(Course.class, 1);
        System.out.print(course.getCname());
        System.out.println("学生姓名:");
        Set<Student> set1 = course.getStudents();
        for (Student s : set1) {
            System.out.println(s.getSname());
        }
    }

    @Test
    public void test10() {
        List<Word> list = session.createQuery("from Word").list();
        for (Word w : list) {
            System.out.println(w.getTitle());
        }
    }

    @Test
    public void test11() {
        Query query = session.createQuery("from Word w where w.tid>7 and w.title like ?");
        query.setString(0, "%标题%");
        List<Word> list = query.list();
        for (Word w : list
        ) {
            System.out.println(w.getTid() + "," + w.getTitle());
        }
    }

    @Test
    public void test12() {
        Query query = session.createQuery("select w.title,w.context from Word w");
        List<Object[]> list = query.list();
        for (Object[] obj : list) {
            System.out.println("标题:" + obj[0] + "\t 内容:" + obj[1]);
        }
    }

    @Test
    public void test13() {
        Query query = session.createQuery("select new Word(w.id,w.title) from Word w");
        List<Word> list = query.list();
        for (Word w : list) {
            System.out.println("ID:" + w.getTid() + "\t标题:" + w.getTitle() + "\t内容:" + w.getContext());
        }
    }

    @Test
    public void test14() {
        Query query = session.createQuery("select w.id,w.title from Word w where tid>? and title like ?");
        query.setInteger(0, 5);
        query.setString(1, "%标题%");
        List<Object[]> list = query.list();
        for (Object[] obj : list) {
            System.out.println(obj[0] + "\t" + obj[1]);
        }
    }

    @Test
    public void test15() {
        Query query = session.createQuery("from Topic t " +
                "where title like :title " +
                "and tid between :tlow and :thigh " +
                "and ttime < :time");
        Map properties = new HashMap();
        properties.put("title", "%李非凡%");
        properties.put("tlow", 26);
        properties.put("thigh", 28);
        properties.put("time", new Date());
        query.setProperties(properties);
        List<Topic> list = query.list();
        for (Topic t : list) {
            System.out.println(t.getTitle() + "\t" + t.getContext());
        }
    }

    @Test
    public void test16() {
        Query query = session.createQuery("select count(t) from Topic t where t.user.id=:uid");
        query.setInteger("uid", 1);
        Long lo = (Long) query.uniqueResult();
        System.out.println(lo);
    }

    @Test
    public void test17() {
        Query query = session.createQuery("from Topic t ");
        query.setFirstResult(0);
        query.setMaxResults(3);
        List<Topic> list = query.list();
        for (Topic t : list) {
            System.out.println(t.getTitle());
        }
    }

    @Test
    public void test18() {
        Query query = session.createQuery("select t.title,max(id),min(id) from Topic t group by t.user.id");
        List<Object[]> list = query.list();
        for (Object[] obj : list) {
            System.out.println("标题:" + obj[0] + ",MAX_ID:" + obj[1] + ",MIN_ID" + obj[2]);
        }

    }

    @Test
    public void test19() {
        Query query = session.createQuery("select title,user.id from Topic order by id desc");
        List<Object[]> list = query.list();
        for (Object[] obj : list) {
            System.out.println("UID:" + obj[1] + ",标题:" + obj[0]);
        }
    }

    @Test
    public void test20() {
        Query query = session.createQuery("select count(*) from Topic ");
        Long count = (Long) query.uniqueResult();
        System.out.println(count);
    }

    @Test
    public void test21() {
        Query query = session.createQuery("select id,user.uname from Topic where id=(select max(tid) from Topic)");
        List<Object[]> list = query.list();
        for (Object[] obj : list) {
            System.out.println("TITLE:" + obj[0] + ",UNAME:" + obj[1]);
        }
    }



    @After
    public void aft() {
        session.close();
    }
}
